package com.learnjava.util;

import java.sql.*;

/**
 * @Description: 数据库操作类
 * @Author: xiangty
 * @Date: 2018-03-30
 */
public class DBUtil {

    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    private static final String DRIVER_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    private static final String DRIVER_USER = "root";
    private static final String DRIVER_PWD = "123456";

    protected Connection conn;
    protected PreparedStatement pst;
    protected ResultSet rs;


    /**
     * 配置驱动
     */
    static {
        try {
            Class.forName(DRIVER_CLASS);
//            System.out.println("配置驱动成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 链接数据库
     *
     * @return
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(DRIVER_URL, DRIVER_USER, DRIVER_PWD);
//            System.out.println("链接数据库成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 查询方法
     *
     * @param sql    查询的sql语句, sql中可以带问号,例如:"select * from table_name where id=?;"
     * @param params 传入的参数内容数组
     * @return
     */
    public ResultSet commQuery(String sql, Object... params) {
        conn = getConnection();
        try {
            pst = conn.prepareStatement(sql);
            if (params != null && params.length > 0) {
                setValues(pst, params);
            }
            rs = pst.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 给sql中的参数赋值
     *
     * @param pst
     * @param params 参数数组
     */
    private void setValues(PreparedStatement pst, Object... params) {
        for (int i = 0; i < params.length; i++) {
            try {
                pst.setObject(i + 1, params[i]);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 增删改 方法
     *
     * @param sql    增删改的sql
     * @param params 传入的参数
     * @return 返回被操作的条数
     */
    public int commUpdate(String sql, Object... params) {
        int result = 0;
        conn = getConnection();
        try {
            pst = conn.prepareStatement(sql);
            if (params != null && params.length > 0) {
                setValues(pst, params);
            }
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, pst, rs);
        }
        return result;
    }

    /**
     * 关闭Connection, PreparedStatement, ResultSet
     */
    public void closeAll(Connection conn, PreparedStatement pst, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pst != null) {
                pst.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


//    public static void main(String[] args) {
//        new DBUtil().getConnection();
//    }

}
